package com.music.mapper;

import com.music.pojo.SingleSong;
import com.music.pojo.SingleSongExample;
import java.util.List;

import com.music.utils.SingleSongInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

public interface SingleSongMapper {
    @SelectProvider(type=SingleSongSqlProvider.class, method="countByExample")
    long countByExample(SingleSongExample example);

    @DeleteProvider(type=SingleSongSqlProvider.class, method="deleteByExample")
    int deleteByExample(SingleSongExample example);

    @Delete({
        "delete from music",
        "where mid = #{mid,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer mid);

    @Insert({
        "insert into music (mid, mname, ",
        "source, pic, publish_time, ",
        "alid, sid, lyc)",
        "values (#{mid,jdbcType=INTEGER}, #{mname,jdbcType=VARCHAR}, ",
        "#{source,jdbcType=VARCHAR}, #{pic,jdbcType=VARCHAR}, #{publishTime,jdbcType=DATE}, ",
        "#{alid,jdbcType=INTEGER}, #{sid,jdbcType=INTEGER}, #{lyc,jdbcType=VARCHAR})"
    })
    int insert(SingleSong row);

    @InsertProvider(type=SingleSongSqlProvider.class, method="insertSelective")
    int insertSelective(SingleSong row);

    @SelectProvider(type=SingleSongSqlProvider.class, method="selectByExample")
    @Results({
        @Result(column="mid", property="mid", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="mname", property="mname", jdbcType=JdbcType.VARCHAR),
        @Result(column="source", property="source", jdbcType=JdbcType.VARCHAR),
        @Result(column="pic", property="pic", jdbcType=JdbcType.VARCHAR),
        @Result(column="publish_time", property="publishTime", jdbcType=JdbcType.DATE),
        @Result(column="alid", property="alid", jdbcType=JdbcType.INTEGER),
        @Result(column="sid", property="sid", jdbcType=JdbcType.INTEGER),
        @Result(column="lyc", property="lyc", jdbcType=JdbcType.VARCHAR)
    })
    List<SingleSong> selectByExample(SingleSongExample example);

    @Select({
        "select",
        "mid, mname, source, pic, publish_time, alid, sid, lyc",
        "from music",
        "where mid = #{mid,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column="mid", property="mid", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="mname", property="mname", jdbcType=JdbcType.VARCHAR),
        @Result(column="source", property="source", jdbcType=JdbcType.VARCHAR),
        @Result(column="pic", property="pic", jdbcType=JdbcType.VARCHAR),
        @Result(column="publish_time", property="publishTime", jdbcType=JdbcType.DATE),
        @Result(column="alid", property="alid", jdbcType=JdbcType.INTEGER),
        @Result(column="sid", property="sid", jdbcType=JdbcType.INTEGER),
        @Result(column="lyc", property="lyc", jdbcType=JdbcType.VARCHAR)
    })
    SingleSong selectByPrimaryKey(Integer mid);

    @UpdateProvider(type=SingleSongSqlProvider.class, method="updateByExampleSelective")
    int updateByExampleSelective(@Param("row") SingleSong row, @Param("example") SingleSongExample example);

    @UpdateProvider(type=SingleSongSqlProvider.class, method="updateByExample")
    int updateByExample(@Param("row") SingleSong row, @Param("example") SingleSongExample example);

    @UpdateProvider(type=SingleSongSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(SingleSong row);

    @Update({
        "update music",
        "set mname = #{mname,jdbcType=VARCHAR},",
          "source = #{source,jdbcType=VARCHAR},",
          "pic = #{pic,jdbcType=VARCHAR},",
          "publish_time = #{publishTime,jdbcType=DATE},",
          "alid = #{alid,jdbcType=INTEGER},",
          "sid = #{sid,jdbcType=INTEGER},",
          "lyc = #{lyc,jdbcType=VARCHAR}",
        "where mid = #{mid,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(SingleSong row);

    @Select("select * from music where alid = #{alid}")
    List<SingleSong> findSongsByAlid(String alid);

    @Select("select * from music where mid = #{mid}")
    SingleSong findByMid(int mid);

    @Select("select * from music where mid in (select mid from playlist_and_music where plid= #{plid})")
    @Results(value = {
            @Result(id = true,column = "mid",property = "mid"),
            @Result(column = "mname",property = "mname"),
            @Result(column = "source",property = "source"),
            @Result(column = "pic",property = "pic"),
            @Result(column = "publish_time",property = "publishTime"),
            @Result(column = "lyc",property = "lyc"),
            @Result(column = "sid",property = "singer",one = @One(select = "com.music.mapper.SingerMapper.findBySid")),
            @Result(column = "mid",property = "album",one = @One(select = "com.music.mapper.AlbumMapper.findByMid")),
    })
    List<SingleSong> findByplid(int plid);

    /*
    查询歌曲名字包含输入
    或 歌曲作者名包含输入
    或 歌曲类型等于输入
    并按歌曲的用户收藏数进行倒序的前5首歌曲
    */
    @Select("SELECT *, (SELECT COUNT(DISTINCT uhp.uid) " +
            "FROM playlist_and_music AS pam " +
            "INNER JOIN user_has_playlist AS uhp ON pam.plid = uhp.plid " + // 添加空格
            "WHERE pam.mid = music.mid) AS fans " + // 添加空格
            "FROM music " + // 添加空格
            "WHERE mname LIKE CONCAT('%', #{search}, '%') " + // 使用占位符并添加空格
            "OR mid IN (SELECT mid FROM music_category WHERE cid IN (SELECT cid FROM category WHERE cname LIKE CONCAT('%', #{search}, '%'))) " + // 使用占位符并添加空格
            "OR sid IN (SELECT sid FROM singer WHERE sname LIKE CONCAT('%', #{search}, '%')) " + // 使用占位符并添加空格
            "ORDER BY fans DESC " + // 添加空格
            "LIMIT 0, 5") // 添加空格
    @Results(id = "songMap",value = {
            @Result(id = true,column = "mid",property = "mid"),
            @Result(column = "mname",property = "mname"),
            @Result(column = "source",property = "source"),
            @Result(column = "pic",property = "pic"),
            @Result(column = "publish_time",property = "publishTime"),
            @Result(column = "lyc",property = "lyc"),
            @Result(column = "alid",property = "album",one = @One(select = "com.music.mapper.AlbumMapper.findByAlid")),
            @Result(column = "sid",property = "singer",one = @One(select = "com.music.mapper.SingerMapper.findBySid")),
            @Result(column = "mid",property = "categorys",many = @Many(select = "com.music.mapper.CategoryMapper.findByMid"))
    })
    List<SingleSong> findWithCategorysAndSingerAndAlbum(String param);

    /*
    查询歌曲名字包含输入
    或 歌曲作者名包含输入
    或 歌曲类型等于输入
    并按歌曲的用户收藏数进行倒序的歌曲
    */
    @Select("SELECT *, (SELECT COUNT(DISTINCT uhp.uid) " +
            "FROM playlist_and_music AS pam " +
            "INNER JOIN user_has_playlist AS uhp ON pam.plid = uhp.plid " + // 添加空格
            "WHERE pam.mid = music.mid) AS fans " + // 添加空格
            "FROM music " + // 添加空格
            "WHERE mname LIKE CONCAT('%', #{search}, '%') " + // 使用占位符并添加空格
            "OR mid IN (SELECT mid FROM music_category WHERE cid IN (SELECT cid FROM category WHERE cname LIKE CONCAT('%', #{search}, '%'))) " + // 使用占位符并添加空格
            "OR sid IN (SELECT sid FROM singer WHERE sname LIKE CONCAT('%', #{search}, '%')) " + // 使用占位符并添加空格
            "ORDER BY fans DESC ")
    @ResultMap("songMap")
    List<SingleSong> findSingleSongsBySearch(String search);

    /*
    *被com.music.mapper.SingerMapper.findWithFollowsAndAlbumsBySid 调用
    *查询歌手的歌曲
    */
    @Select("select * from music where sid = #{sid}")
    List<SingleSong> findSingleSongsBySid(Integer sid);


    @Select("select * from music where mid = #{mid}")
    @Results(value = {
            @Result(id = true,column = "mid",property = "mid"),
            @Result(column = "mname",property = "mname"),
            @Result(column = "source",property = "source"),
            @Result(column = "pic",property = "pic"),
            @Result(column = "publish_time",property = "publishTime"),
            @Result(column = "lyc",property = "lyc"),
            @Result(column = "sid",property = "singer",one = @One(select = "com.music.mapper.SingerMapper.findBySid")),
            @Result(column = "mid",property = "comments",many = @Many(select = "com.music.mapper.CommentMapper.findWithUser"))
    })
    SingleSong findWithCommentsAndSingerByMid(String mid);

    @Select("select count(*) from playlist_and_music where mid = #{mid} and plid in (select plid from playlist where uid = #{uid})")
    Integer isFavorite(@Param("mid") Integer mid,@Param("uid") Integer uid);


    @Select("select mid,count(timestamp) as count from listening_history where uid = #{uid} group by mid")
    List<SingleSongInfo> findSingleSongInfo(Integer uid);

    @Select("select * from music")
    @ResultMap("songMap")
    List<SingleSong> findAll();

    //查找歌手当前10首热门歌曲
    @Select("select * from music where sid = #{sid} and mid in (select mid from comment group by mid order by count(*) desc) limit 10")
    List<SingleSong> findHotSongs(Integer sid);

    @Select("select count(*) from music where sid = #{sid};")
    Integer findSongsCount(Integer sid);

    @Select("select pic from music where mid = #{mid};")
    String findPicByMid(Integer mid);

    String findPicBy(Integer mid);

    //飙升榜
    @Select("(select *,IFNULL((select Count(timestamp) from listening_history where music.mid = listening_history.mid " +
            "AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY) group by mid),0) counts from music order by counts desc limit 0,100)")
    @Results(id = "rank",value = {
            @Result(id = true,column = "mid",property = "mid"),
            @Result(column = "mname",property = "mname"),
            @Result(column = "source",property = "source"),
            @Result(column = "pic",property = "pic"),
            @Result(column = "publish_time",property = "publishTime"),
            @Result(column = "lyc",property = "lyc"),
            @Result(column = "sid",property = "singer",one = @One(select = "com.music.mapper.SingerMapper.findBySid")),
    })
    List<SingleSong> findSoaringSongRank();

    //新歌榜
    @Select("(select *,IFNULL((select Count(timestamp) from listening_history where music.mid = listening_history.mid " +
            "AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY) group by mid),0) counts from music where publish_time >= DATE_SUB(NOW(),INTERVAL 30 DAY) order by counts desc limit 0,100)")
    @ResultMap("rank")
    List<SingleSong> findNewSongRank();

    //热歌榜
    @Select("(select *,IFNULL((select Count(timestamp) from listening_history where music.mid = listening_history.mid " +
            "AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY) group by mid),0) counts from music order by counts desc limit 0,100)")
    @ResultMap("rank")
    List<SingleSong> findHotSongRank();

    @Select("select * from music where sid in (select sid from user_follow_singer where uid = #{uid}) and music.publish_time>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) order by publish_time desc")
    @ResultMap("songMap")
    List<SingleSong> findUserFollowSingerSongs(Integer uid);
}